﻿Imports System.Data.SqlClient
Imports System.Xml
Imports System.Text
Imports System.Security.Cryptography
Imports System.IO
Imports xml_file.obsluga_pliku

Public Class Logowanie
    Public licznik As Integer = 1
    Public dataPass As String = ""
    Public XMLfile As New xml_file.obsluga_pliku

    Public ADRES_SERWER As String
    Public NAZWA_BAZY As String

    'ladowanie okna
    Private Sub logowanie_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        ' NFO: sprawdzamy czy istnieje plik dbConnect.xml
        Dim sprCzyInstniejDBConnectXML As FileInfo = New FileInfo(Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData) + "\w_prog\dbConnect.xml")

        If (Not My.Computer.FileSystem.DirectoryExists(Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData) + "\w_prog")) Then
            My.Computer.FileSystem.CreateDirectory(Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData) + "\w_prog")
        End If

        If sprCzyInstniejDBConnectXML.Exists Then
            'xml_plik_db_connect_read()
            ADRES_SERWER = XMLfile.xml_plik_db_connect_read("addr_serwer").ToString
            NAZWA_BAZY = XMLfile.xml_plik_db_connect_read("db")

            CONN_String = "Data Source=" + ADRES_SERWER + ";Initial Catalog=" + NAZWA_BAZY + ";User ID=" + DB_User + ";Password=" + DB_Password + ";Connect Timeout=15"

            If ADRES_SERWER = String.Empty Then
                L_baza_danych.Text = "Baza danych: [KO]"
                L_serwer.Text = "Serwer: [KO]"
            Else
                TB_Adres_Serwera.Text = ADRES_SERWER
                TB_Baza_Danych.Text = NAZWA_BAZY
            End If
        Else
            MsgBox("Nie istnieje plik konfiguracyjny połączenia z bazą danych. Użyj przycisku opcji i podaj wymagane dane", MsgBoxStyle.Information)
            B_Zaloguj.Enabled = False
        End If

        SprLogin(sender, e)
        SprPass(sender, e)

        Me.Height = 219
    End Sub

    'akcja przy zmiania tekstu w polu nazwa użytkownika
    Private Sub SprLogin(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TB_Username.TextChanged
        If (TB_Username.Text = String.Empty) Then
            B_Zaloguj.Enabled = False
        Else
            If (TB_Password.Text = String.Empty) Then
                B_Zaloguj.Enabled = False
            Else
                B_Zaloguj.Enabled = True
            End If
        End If
    End Sub

    'akcja przy zmiania tekstu w polu hasło użytkownika
    Private Sub SprPass(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TB_Password.TextChanged
        If (TB_Password.Text = String.Empty) Then
            B_Zaloguj.Enabled = False
        Else
            If (TB_Username.Text = String.Empty) Then
                B_Zaloguj.Enabled = False
            Else
                B_Zaloguj.Enabled = True
            End If
        End If
    End Sub

    'wyświetlenie opcji w oknie logowania bądz ich ukrycie
    Private Sub Opcje_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles B_Opcje.Click
        If ((licznik Mod 2) = 0) Then
            Me.Height = 219
            B_Opcje.Text = "...opcje >>"
        Else
            Me.Height = 313
            B_Opcje.Text = "...opcje <<"
        End If
        licznik += 1
    End Sub

    'akcja wywołana przez naciśniejęcie przycisku logowania
    Private Sub Zaloguj_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles B_Zaloguj.Click
        If sprPolaczenie() Then
            If (LoginExist(TB_Username.Text)) Then
                If (sprUser()) Then
                    If uprawnienia() Then
                        'zapis_do_logu("B003", DB_id_user, "")
                        dataLogowaniaOK()
                        Me.Close()
                        If Not dataSerwera() Then
                            zmienPass.MdiParent = MDI_Parent
                            zmienPass.wymuszonaZmianaHasla = 1

                            'If (MDI_Parent.MenuStripUser.IsHandleCreated) Then
                            '    MDI_Parent.MenuStripUser.Enabled = False
                            'End If

                            zmienPass.Show()
                        End If

                    Else
                        MsgBox("Konto zablokowane. Skontaktuj się z administratorem aby odblokować konto.", MsgBoxStyle.Critical, "Konto zablokowane")
                        'zapis_do_logu("B002", DB_id_user, "próba logowania użytkownika: " + TB_Username.Text)
                    End If
                ElseIf Not sprUser() Then
                    'zapis_do_logu("B001", DB_id_user, "")
                    dataLogowanieKO()
                    MsgBox("Popraw nazwę użytkownika lub hasło", MsgBoxStyle.Critical, "Błąd logowania")
                End If
            Else
                'zapis_do_logu("B001", 0, "próba logowania użytkownika o loginie:" + TB_Username.Text)
                MsgBox("Poprawna nazwę użytkownika lub hasło", MsgBoxStyle.Critical, "Błąd logowania")
            End If
        End If
    End Sub

    'prodecura dotycząca uprawnień 
    Private Function uprawnienia() As Boolean
        Dim SQLCCommand As New SqlCommand
        Dim reader As SqlDataReader

        Dim querySELECT As String = "SELECT " + _
                                    "[imie], [nazwisko], [blokada], [lvl], [dLogin_last], [dLogin_err], [dLogin_aktualna] " + _
                                    "FROM [users] " + _
                                    "WHERE id=@id"

        SQLCCommand.Parameters.Add("@id", SqlDbType.VarChar).Value = DB_id_user
        SQLCCommand.CommandText = querySELECT
        SQLCCommand.CommandType = CommandType.Text
        SQLCCommand.Connection = New SqlConnection(CONN_String)

        SQLCCommand.Connection.Open()
        reader = SQLCCommand.ExecuteReader()
        reader.Read()

        DB_imie = Convert.ToString(reader(0))
        DB_nazwisko = Convert.ToString(reader(1))
        DB_blokada = Convert.ToInt32(reader(2))
        DB_lvl = Convert.ToInt32(reader(3))
        DB_dLogin_last = Convert.ToString(reader(4))
        DB_dLogin_err = Convert.ToString(reader(5))
        DB_dLogin_aktualna = Convert.ToString(reader(6))

        SQLCCommand.Connection.Close()

        ' uprawnienia
        If (DB_blokada = 1) Then
            Return False
        ElseIf (DB_blokada = 0) Then
            MDI_Parent.FormBorderStyle = Windows.Forms.FormBorderStyle.Sizable

            MDI_Parent.MenuStrip.Visible = True
            MDI_Parent.StatusStrip.Visible = True
            MDI_Parent.ToolStrip.Visible = True

            'spr czy administrator czy użytkownik

            If (DB_lvl = 0) Then
                'ADMINISTRATOR

            ElseIf (DB_lvl = 1) Then
                'UŻYTKOWNIK

            End If

            Return True
        End If
    End Function

    'sprawdzenie użytkownika
    Private Function sprUser() As Boolean
        Dim SQLCCommand As New SqlCommand
        Dim reader As SqlDataReader
        Dim hashPass As String = ""

        Dim pass As String = ""

        Dim querySELECT As String = "SELECT SUBSTRING(master.dbo.fn_varbintohexstr(HashBytes('SHA1', @pass)), 3, 50)"

        SQLCCommand.Parameters.Add("@pass", SqlDbType.VarChar).Value = TB_Password.Text
        SQLCCommand.CommandText = querySELECT
        SQLCCommand.CommandType = CommandType.Text
        SQLCCommand.Connection = New SqlConnection(CONN_String)

        SQLCCommand.Connection.Open()
        reader = SQLCCommand.ExecuteReader()
        reader.Read()
        hashPass = Convert.ToString(reader(0))
        SQLCCommand.Connection.Close()

        'drugi
        querySELECT = najnowszeHasloUzytkownika

        SQLCCommand.Parameters.Add("@login", SqlDbType.VarChar).Value = TB_Username.Text
        SQLCCommand.CommandText = querySELECT
        SQLCCommand.CommandType = CommandType.Text
        SQLCCommand.Connection = New SqlConnection(CONN_String)

        SQLCCommand.Connection.Open()
        reader = SQLCCommand.ExecuteReader()
        reader.Read()

        DB_id_user = Convert.ToInt32(reader(0))
        pass = Convert.ToString(reader(1))
        dataPass = Convert.ToString(reader(2))

        SQLCCommand.Connection.Close()

        If (hashPass = pass) Then
            Return True
        ElseIf (hashPass <> pass) Then
            Return False
        End If
    End Function

    Private Function LoginExist(ByVal UserName As String) As Boolean
        Dim SQLCCommand As New SqlCommand
        Dim querySELECT As String = "SELECT COUNT(id) FROM [users] WHERE login=@login;"

        SQLCCommand.Parameters.Add("@login", SqlDbType.VarChar).Value = UserName
        SQLCCommand.CommandText = querySELECT

        If (sql_ileWynikow(SQLCCommand) = 1) Then
            Return True
        Else
            Return False
        End If
    End Function

    'data logowania
    Private Sub dataLogowaniaOK()
        Dim aktualnaData As String = ""
        Dim querySELECT As String = "SELECT dLogin_aktualna FROM [users] WHERE id=@id"
        Dim SQLCCommand As New SqlCommand
        Dim reader As SqlDataReader

        SQLCCommand.Parameters.Add("@id", SqlDbType.Int).Value = DB_id_user
        SQLCCommand.CommandText = querySELECT
        SQLCCommand.CommandType = CommandType.Text
        SQLCCommand.Connection = New SqlConnection(CONN_String)

        SQLCCommand.Connection.Open()
        reader = SQLCCommand.ExecuteReader()
        reader.Read()
        aktualnaData = Convert.ToString(reader(0))
        SQLCCommand.Connection.Close()

        'drugi

        Dim SQLCCommand2 As New SqlCommand
        Dim queryUPDATE As String = ""

        If (aktualnaData = "") Then
            queryUPDATE = "UPDATE [users]" + _
                "SET [dLogin_last] = GETDATE() " + _
                      ",[dLogin_aktualna] = GETDATE()" + _
                "WHERE id=@id"
        ElseIf (aktualnaData <> "") Then
            queryUPDATE = "UPDATE [users]" + _
                "SET [dLogin_last] = @data_aktualna " + _
                      ",[dLogin_aktualna] = GETDATE()" + _
                "WHERE id=@id"
            SQLCCommand2.Parameters.Add("@data_aktualna", SqlDbType.DateTime).Value = aktualnaData
        End If

        SQLCCommand2.Parameters.Add("@id", SqlDbType.Int).Value = DB_id_user

        SQLCCommand2.CommandText = queryUPDATE
        SQLCCommand2.CommandType = CommandType.Text
        SQLCCommand2.Connection = New SqlConnection(CONN_String)

        SQLCCommand2.Connection.Open()
        SQLCCommand2.ExecuteNonQuery()
        SQLCCommand2.Connection.Close()
    End Sub

    'data logowanie KO
    Private Sub dataLogowanieKO()
        Dim queryUPDATE As String = "UPDATE [users]" + _
                "SET " + _
                "[dLogin_err] = GETDATE()" + _
                "WHERE id=@id"

        Dim SQLCCommand As New SqlCommand
        SQLCCommand.Parameters.Add("@id", SqlDbType.Int).Value = DB_id_user
        SQLCCommand.CommandText = queryUPDATE
        SQLCCommand.CommandType = CommandType.Text
        SQLCCommand.Connection = New SqlConnection(CONN_String)

        SQLCCommand.Connection.Open()
        SQLCCommand.ExecuteNonQuery()
        SQLCCommand.Connection.Close()
    End Sub

    'sprawdzenie połączenia
    Private Function sprPolaczenie() As Boolean
        DB_TimeOut = "Connect Timeout=2;"

        ADRES_SERWER = TB_Adres_Serwera.Text
        NAZWA_BAZY = TB_Baza_Danych.Text

        If (ADRES_SERWER <> String.Empty) And (NAZWA_BAZY <> String.Empty) Then
            CONN_String = "Data Source=" + ADRES_SERWER + ";Initial Catalog=" + NAZWA_BAZY + ";User ID=" + DB_User + ";Password=" + DB_Password + ";" + DB_TimeOut + " "

            Dim objConn As SqlConnection = New SqlConnection(CONN_String)
            Try
                objConn.Open()
                L_serwer.Text = "Serwer: [OK]"
                objConn.Close()
                Try
                    Dim sqlcommand As New SqlCommand
                    Dim querySELECT As String = "SELECT [id] FROM [users]"

                    sqlcommand.CommandText = querySELECT
                    sqlcommand.CommandType = CommandType.Text
                    sqlcommand.Connection = New SqlConnection(CONN_String)
                    sqlcommand.Connection.Open()

                    Dim reader As SqlDataReader
                    reader = sqlcommand.ExecuteReader
                    reader.Read()
                    sqlcommand.Connection.Close()
                    L_baza_danych.Text = "Baza danych: [OK]"
                Catch ex As Exception
                    MsgBox("Podaj poprawną nazwę bazy danych" + ex.ToString, MsgBoxStyle.Critical)
                End Try

                'xml_plik_db_connect_write(TB_serwer.Text, TB_baza_danych.Text)
                XMLfile.xml_plik_db_connect_write(TB_Adres_Serwera.Text, TB_Baza_Danych.Text)
                Return True
            Catch ex As Exception
                L_serwer.Text = "Serwer: [KO]"
                L_baza_danych.Text = "Baza danych: [KO]"

                MsgBox("Popraw dane konfiguracyjne połączenia z bazą danych adres serwera/nazwę bazy danych. ", MsgBoxStyle.Critical, "Błąd konfiguracji")

                Return False
            End Try
        ElseIf (ADRES_SERWER = String.Empty) Or (NAZWA_BAZY = String.Empty) Then
            MsgBox("Wypełnij dane poprawnie", MsgBoxStyle.Critical, "DB Error")

            Return False
        End If

        DB_TimeOut = "Connect Timeout=15;"
    End Function

    'opcja anuluj
    Private Sub Anuluj_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles B_anuluj.Click
        MDI_Parent.Close()
    End Sub

    Private Function dataSerwera() As Boolean
        Dim h_waznosc As Integer = 0

        h_waznosc = sql_zwrot_zapytania_int("SELECT wartosc FROM polityka_bezpieczenstwa WHERE parametr='h_waznosc'")

        Dim zapytanie As String = "DECLARE @data as date " + _
        "DECLARE @data3 as date " + _
        "SET @data = GETDATE () " + _
        "SET @data3 = (SELECT dateadd(day,(@h_waznosc),@data2)) " + _
        "IF ((@data3) > @data ) " + _
        "SELECT 'True' " + _
        "ELSE " + _
        "SELECT 'False' "

        Dim sqlccomand As New SqlCommand

        sqlccomand.Parameters.Add("@data2", SqlDbType.Date).Value = dataPass
        sqlccomand.Parameters.Add("@h_waznosc", SqlDbType.Int).Value = h_waznosc

        sqlccomand.CommandText = zapytanie
        sqlccomand.CommandType = CommandType.Text
        sqlccomand.Connection = New SqlConnection(CONN_String)

        Dim odczytaj As SqlDataReader
        Dim wynik As Boolean

        sqlccomand.Connection.Open()
        odczytaj = sqlccomand.ExecuteReader
        odczytaj.Read()
        wynik = odczytaj(0)
        sqlccomand.Connection.Close()

        Return wynik
    End Function
End Class